package com.android.Bean;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.android.DBTools.ADUS;
import com.android.DBTools.DBConnection;
import com.android.ObjectBean.WaterEleObjectList;

import net.sf.json.JSONArray;

public class WaterEleBean {
	// 查看本用户的水电费费 返回的是每月的水电费json
	public String waterEleView(String genUID) throws ClassNotFoundException, SQLException {
		String sql = "SELECT w.waterCount, w.waterSpend, w.waterMoney, w.eleCount, w.eleSpend, "
				+ "w.eleMoney, w.WEDate, w.WEID, g.genUID FROM waterele w "
				+ "RIGHT JOIN generalusers g ON w.genUID = g.genUID WHERE w.genUID = " + genUID
				+ " ORDER BY w.WEDate DESC LIMIT 0, 3";
		ResultSet rs = ADUS.selectData(sql);
		List<WaterEleObjectList> list = new ArrayList<WaterEleObjectList>();
		while (rs.next()) {
			WaterEleObjectList waterEleObjectList = new WaterEleObjectList();
			waterEleObjectList.setWaterCount(rs.getString(rs.findColumn("waterCount")));
			waterEleObjectList.setWaterSpend(rs.getString(rs.findColumn("waterSpend")));
			waterEleObjectList.setWaterMoney(rs.getString(rs.findColumn("waterMoney")));
			waterEleObjectList.setEleCount(rs.getString(rs.findColumn("eleCount")));
			waterEleObjectList.setEleSpend(rs.getString(rs.findColumn("eleSpend")));
			waterEleObjectList.setEleMoney(rs.getString(rs.findColumn("eleMoney")));
			waterEleObjectList.setWEDate(rs.getString(rs.findColumn("WEDate")));
			waterEleObjectList.setGenUID(rs.getString(rs.findColumn("genUID")));
			list.add(waterEleObjectList);
		}
		DBConnection.closeCon();
		JSONArray json = JSONArray.fromObject(list);
		return json.toString();
	}

	// 插入新的数据 用户交水电费
	public void waterEleInsert(String genUID, String waterMoney, String eleMoney)
			throws ClassNotFoundException, SQLException {
		String sqlSelect = "SELECT waterMoney,waterSpend,eleMoney,eleSpend,WEDate FROM waterele WHERE genUID = "
				+ genUID + " ORDER BY WEDate DESC LIMIT 0,1;";
		ResultSet rs = ADUS.selectData(sqlSelect);
		rs.next();
		int waterSpend = Integer.valueOf(rs.getString(rs.findColumn("waterSpend")));
		int lastwaterMoney = Integer.valueOf(rs.getString(rs.findColumn("waterMoney")));
		int eleSpend = Integer.valueOf(rs.getString(rs.findColumn("eleSpend")));
		int lasteleMoney = Integer.valueOf(rs.getString(rs.findColumn("eleMoney")));
		int currentWaterMoney = Integer.valueOf(waterMoney);
		int currentEleMoney = Integer.valueOf(eleMoney);
		currentWaterMoney = currentWaterMoney + lastwaterMoney - waterSpend;
		currentEleMoney = currentEleMoney + lasteleMoney - eleSpend;

		Date date = new Date();
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
		String WEDate = simpleDateFormat.format(date);
		simpleDateFormat.format(date);
		String sqlInsert = "INSERT INTO waterele(WEID,genUID,waterCount,waterSpend,waterMoney,"
				+ "eleCount,eleSpend,eleMoney,WEDate) VALUES(NULL," + genUID + ",0,0,\"" + currentWaterMoney + "\",0,0,\""
				+ currentEleMoney + "\",\"" + WEDate + "\")";
		System.out.println(sqlInsert);
		ADUS.insertData(sqlInsert);
		DBConnection.closeCon();
	}
}
